<?php
//ini_set("display_errors", "On");
//error_reporting(E_ALL | E_STRICT);

error_reporting(0);  
set_time_limit(0);
require_once './Classes/PHPExcel.php';

$ziDian=array(
    'kswd'=>'进入开始问答测试页',
    'jrysly'=>'进入艺术疗愈页',
    'yslyqy'=>'艺术疗愈起源页',
    'whsys'=>'艺术疗愈为何是艺术',
    'xmdlx'=>'艺术疗愈下面的练习可以帮助你',
    'lx1'=>'进入练习一',
    'lx2'=>'进入练习二',
    'lx3'=>'进入练习三',
    'lx4'=>'进入练习四',
    'lx5'=>'进入练习五',
    'lx6'=>'进入练习六',
    'saveimage1'=>'练习一保存图片',
    'saveimage2'=>'练习二保存图片',
    'saveimage4'=>'练习四保存图片',
);

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', '日期')
            ->setCellValue('B1', '昨日新增访问数')
            ->setCellValue('C1', '总访问次数PV')
            ->setCellValue('D1', '总访问人数UV')
            ->setCellValue('E1', '进入开始问答测试页')
            ->setCellValue('F1', '进入艺术疗愈页')
            ->setCellValue('G1', '艺术疗愈起源页')
            ->setCellValue('H1', '艺术疗愈为何是艺术')
            ->setCellValue('I1', '艺术疗愈下面的练习可以帮助你')
            ->setCellValue('J1', '进入练习一')
            ->setCellValue('K1', '进入练习二')
            ->setCellValue('L1', '进入练习三')
            ->setCellValue('M1', '进入练习四')
            ->setCellValue('N1', '进入练习五')
            ->setCellValue('O1', '进入练习六')
            ->setCellValue('P1', '完成12道题(Pv/Uv)')
            ->setCellValue('Q1', '保存图片(Pv/Uv):练习1，练习2，练习4')
;         
$dbhost = '47.110.131.55';
$dbuser = 'root';
$dbpwd  ='sxy7788123456';
$dbName ='art';

$mysqli = new mysqli($dbhost, $dbuser, $dbpwd, $dbName);
if (!$mysqli->set_charset("utf8")) {printf("Error loading character set utf8: %s\n", $mysqliTwo->error);}
 
 
$sql="SELECT * FROM `tongj` ";
$query =$mysqli->query($sql);
$index=2;
$b1=$c1=$d1=$e1=$f=$g=$h=$i=$j=$k=$l=$m=$n=$o=$p=$q=array('uv'=>0,'pv'=>0);
while ($list=$query->fetch_array()){
        $pvUvshare=json_decode($list['pv_uv_share'],true);
        $ti12=json_decode($list['ti12'],true);
        $allpage=json_decode($list['allpage'],true);
        $image=json_decode($list['image'],true);
    
        
        $theImage=$image[0]['saveimage1']['pv'].'(PV)/'.$image[0]['saveimage1']['uv'].'(UV)'.',';
        $theImage.=$image[1]['saveimage2']['pv'].'(PV)/'.$image[1]['saveimage2']['uv'].'(UV)'.',';
        $theImage.=$image[2]['saveimage4']['pv'].'(PV)/'.$image[2]['saveimage4']['uv'].'(UV)';
        
        $q['pv']=$q['pv'] + $image[0]['saveimage1']['pv']+$image[1]['saveimage2']['pv']+$image[2]['saveimage4']['pv'];
        $q['uv']=$q['uv']+$image[0]['saveimage1']['uv']+$image[1]['saveimage2']['uv']+$image[2]['saveimage4']['uv'];
        
        
        $b1['uv']=$b1['uv']+$pvUvshare['newuv'];
        $c1['pv']=$pvUvshare['pv'] + $c1['pv'];
        $d1['uv']=$pvUvshare['uv'] + $d1['uv'];
        $e1['pv']=$allpage['kswd']['pv'] + $e1['pv'];
        $e1['uv']=$allpage['kswd']['uv'] + $e1['uv'];
        
        $f['pv']=$allpage['jrysly']['pv'] + $f['pv'];
        $f['uv']=$allpage['jrysly']['uv'] + $f['uv'];
        
        $g['pv']=$allpage['yslyqy']['pv'] + $g['pv'];
        $g['uv']=$allpage['yslyqy']['uv'] + $g['uv'];
        $h['pv']=$allpage['whsys']['pv'] + $h['pv'];
        $h['uv']=$allpage['whsys']['uv'] + $h['uv'];
        $i['pv']=$allpage['xmdlx']['pv'] + $i['pv'];
        $i['uv']=$allpage['xmdlx']['uv'] + $i['uv'];
        $j['pv']=$allpage['lx1']['pv'] + $j['pv'];
        $j['uv']=$allpage['lx1']['uv'] + $j['uv'];
        
        
        $k['pv']=$allpage['lx2']['pv'] + $k['pv'];
        $k['uv']=$allpage['lx2']['uv'] + $k['uv'];
        
        $l['pv']=$allpage['lx3']['pv'] + $l['pv'];
        $l['uv']=$allpage['lx3']['uv'] + $l['uv'];
        
        $m['pv']=$allpage['lx4']['pv'] + $m['pv'];
        $m['uv']=$allpage['lx4']['uv'] + $m['uv'];
        
        $n['pv']=$allpage['lx5']['pv'] + $n['pv'];
        $n['uv']=$allpage['lx5']['uv'] + $n['uv'];
        $o['pv']=$allpage['lx5']['pv'] + $o['pv'];
        $o['uv']=$allpage['lx5']['uv'] + $o['uv'];
        
        $p['pv']=$ti12['pv'] + $p['pv'];
        $p['uv']=$ti12['uv'] + $p['uv'];
        
        
        
        
        
        
	    $objPHPExcel->setActiveSheetIndex(0)
	    ->setCellValue('A'.$index, $list['theday'])
	    ->setCellValue('B'.$index, $pvUvshare['newuv'])
	    ->setCellValue('C'.$index, $pvUvshare['pv'])
	    ->setCellValue('D'.$index, $pvUvshare['uv'])
	    ->setCellValue('E'.$index, $allpage['kswd']['pv'].'/'.$allpage['kswd']['uv'])
	    ->setCellValue('F'.$index, $allpage['jrysly']['pv'].'/'.$allpage['jrysly']['uv'])
	    ->setCellValue('G'.$index, $allpage['yslyqy']['pv'].'/'.$allpage['yslyqy']['uv'])
	    ->setCellValue('H'.$index, $allpage['whsys']['pv'].'/'.$allpage['whsys']['uv'])
	    ->setCellValue('I'.$index, $allpage['xmdlx']['pv'].'/'.$allpage['xmdlx']['uv'])
	    ->setCellValue('J'.$index, $allpage['lx1']['pv'].'/'.$allpage['lx1']['uv'])
	    ->setCellValue('K'.$index, $allpage['lx2']['pv'].'/'.$allpage['lx2']['uv'])
	    ->setCellValue('L'.$index, $allpage['lx3']['pv'].'/'.$allpage['lx3']['uv'])
	    ->setCellValue('M'.$index, $allpage['lx4']['pv'].'/'.$allpage['lx4']['uv'])
	    ->setCellValue('N'.$index, $allpage['lx5']['pv'].'/'.$allpage['lx5']['uv'])
	    ->setCellValue('O'.$index, $allpage['lx6']['pv'].'/'.$allpage['lx6']['uv'])
	    ->setCellValue('P'.$index, $ti12['pv'].'(PV)/'.$ti12['uv'].'(UV)')
	    ->setCellValue('Q'.$index, $theImage)
	    ;
	    
	   
	    
	   
	    $index++;
	 
    
}
$index++;
$index++;

$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$index, '汇总')
->setCellValue('B'.$index, $b1['uv'])
->setCellValue('C'.$index, $c1['pv'])
->setCellValue('D'.$index, $d1['uv'])
->setCellValue('E'.$index, $e1['pv'].'/'.$e1['uv'])
->setCellValue('F'.$index, $f['pv'].'/'.$f['uv'])
->setCellValue('G'.$index, $g['pv'].'/'.$g['uv'])
->setCellValue('H'.$index, $h['pv'].'/'.$h['uv'])
->setCellValue('I'.$index, $i['pv'].'/'.$i['uv'])
->setCellValue('J'.$index, $j['pv'].'/'.$j['uv'])
->setCellValue('K'.$index, $k['pv'].'/'.$k['uv'])
->setCellValue('L'.$index, $l['pv'].'/'.$l['uv'])
->setCellValue('M'.$index, $m['pv'].'/'.$m['uv'])
->setCellValue('N'.$index, $n['pv'].'/'.$n['uv'])
->setCellValue('O'.$index, $o['pv'].'/'.$o['uv'])
->setCellValue('P'.$index, $p['pv'].'(PV)/'.$p['uv'].'(UV)')
->setCellValue('Q'.$index, $q['pv'].'/'.$q['uv'])
;
 
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('用户信息');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=用户信息'.date("Y-m-d").'.xlsx');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
